# -*- coding: utf-8 -*-
"""
Created on Sun Apr 02 11:07:08 2017

@author: Tubin Huang
"""

import mysql.connector
import time

def createDB(dataInfo = {'user':'root','password':'root','host':'localhost','database':'test'}):
    return Database(dataInfo)
def createDatabaseWithTimestamp(dataInfo = {'user':'root','password':'root','host':'localhost','database':'test'}):
    return DatabaseWithTimestamp(dataInfo)
    
class Database(object):
    dataInfo = dict()
    def __init__(self,dataInfo = {'user':'root','password':'root','host':'localhost','database':'test'}):
        self.dataInfo['user'] = dataInfo['user']
        self.dataInfo['password'] = dataInfo['password']
        self.dataInfo['host']= dataInfo['host']
        self.dataInfo['database'] = dataInfo['database']
    def setDBInfo( self, dataInfo ):
        self.dataInfo['user'] = dataInfo['user']
        self.dataInfo['password'] = dataInfo['password']
        self.dataInfo['host']= dataInfo['host']
        self.dataInfo['database'] = dataInfo['database']
    def getConnAndCursor(self):
        conn = mysql.connector.connect(user = self.dataInfo['user'], password = self.dataInfo['password'], database =  self.dataInfo['database'], use_unicode=True)
        cursor = conn.cursor()
        return conn,cursor
    def getdataInfo(self):
        return self.dataInfo
    def jointArgs(self, args, encloseL = "", encloseR = "", split = ","):
        _str = encloseL + str(args[0]) + encloseR
        for arg in args[1:]:
            _str = _str + split + encloseL + str(arg) + encloseR
        return _str
    def dict2list(self,datadict):
        keys = datadict.keys()
        values = []
        for key in keys:
            values.append(datadict[key])
        return keys,values
    def insert(self, tableName, datadicts ):
        conn,cursor = self.getConnAndCursor()
        for datadict in datadicts:
            fields,values = self.dict2list(datadict)
            _cmdField = self.jointArgs(fields)
            _cmdValues = self.jointArgs( values, encloseL = "\'", encloseR = "\'", split = "," )
            cursor.execute("insert ignore into %s.%s (%s) values (%s)" % (self.dataInfo['database'], tableName, _cmdField, _cmdValues))
        conn.commit()
        cursor.close
        conn.close()
    def createLimiterCmd(self, limiter = ""):
        comdLimiter = ""
        if len(limiter)>0:
            dictItems = limiter.items()
            for item in dictItems:
                comdLimiter = comdLimiter + item[0] + " like " + self.jointArgs([item[1]], encloseL = "\'", encloseR = "\'") + " and "
            comdLimiter = 'where ' + comdLimiter[:-4]
        return comdLimiter    
    def update( self, tableName, datadict, limiter = dict()):
        conn,cursor = self.getConnAndCursor()
        cmdlist = []
        for key in datadict.keys():
            cmdlist.append(key + "=" + "\'" + str(datadict[key]) + "\'")
        cmd = self.jointArgs(cmdlist)
        cmdLimiter = self.createLimiterCmd( limiter )
        cursor.execute("update %s.%s set %s  %s" %(self.dataInfo['database'],tableName,cmd, cmdLimiter))
        conn.commit()
        cursor.close()
        conn.close()
    def select( self, tableName, fields = ['*'], limiter = dict()):
        conn,cursor = self.getConnAndCursor()
        _fields = fields
        if _fields == ['*']:
            cursor.execute("show columns from %s.%s" % ( self.dataInfo['database'],tableName) )
            temp = cursor.fetchall()
            _fields = [x[0] for x in temp]
        cmdLimiter = self.createLimiterCmd( limiter )
        cmdFields = self.jointArgs( _fields )
        cursor.execute("select %s from %s.%s %s" % (cmdFields, self.dataInfo['database'], tableName, cmdLimiter))
        values = cursor.fetchall()
        cursor.close()
        conn.close()
        valueDicts = []
        for value in values:
            valueDicts.append( dict(zip(_fields,value)))
        return valueDicts,_fields
        
    def createTable(self,tableName='test', fieldict = {'id':'int unsigned not null auto_increment primary key','update_time':'varchar(45)','name':'varchar(45)','age':'varchar(45)'} ):
        _cmdField = ''
        for key in fieldict.iterkeys():
            _cmdField = _cmdField + str(key) + ' ' + str(fieldict[key]) + ','
        _cmdField = _cmdField[:-1]
        conn,cursor = self.getConnAndCursor()
        cursor.execute("create table %s.%s ( %s )" % (self.dataInfo['database'], tableName, _cmdField) )
        conn.commit()
        cursor.close()
        conn.close()           

class DatabaseWithTimestamp(Database):
    def insert(self, tableName, datadicts):
        _datadicts = []
        for datadict in datadicts:
            datadict['update_time'] = time.ctime()
            _datadicts.append(datadict)
        Database.insert( self,tableName, _datadicts)
    def update(self, tableName, datadict, limiter = dict()):
        _datadict = datadict
        _datadict['update_time'] = time.ctime()
        Database.update(self,tableName, datadict, limiter)
        
        

def test():
    database = DatabaseWithTimestamp()
#    database.createTable()
#    database.insert('test',[{'name':'huang','age':21},{'name':'ZD','age':20},{'age':31}])
#    database.update('test',{'name':'Badboy'},{'id':3})
#   print database.select('test',['id','name','age','update_time'],{'name':'badboy'})

if __name__ == '__main__':
    test()
    